﻿/*
{
"状态":1,
"名称":"生成-新增cmd脚本",
"作者":"马学兵",
"创建时间":"2018-07-21",
"功能":"生成-新增cmd脚本",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/

DECLARE @table_name VARCHAR(250)=''
DECLARE @Values NVARCHAR(max)=''
DECLARE @Insert NVARCHAR(max)=''
DECLARE @Declare NVARCHAR(max)=''
DECLARE @Set NVARCHAR(max)=''
DECLARE @Rst NVARCHAR(MAX)=''

set @table_name='{table_name}'

if isnull(@table_name,'')=''
begin 
	RAISERROR ('SQL:表名不允许为空', 16 ,1 ) ; 
	return 
end 

if not exists(select 1 from y_table where table_name=@table_name)
begin 
	RAISERROR ('SQL:表名不存在', 16 ,1 ) ; 
	return 
end
 
SELECT @Declare=@Declare+'DECLARE @'+(case when datatype in ('real','float','smallmoney','varbinary','binary','char','nchar') then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+')'
              when datatype in ('nvarchar','varchar') and collength=-1 then  LOWER(colname)+' '+datatype+'(max)'
              when datatype in ('nvarchar','varchar') and collength<>-1 then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+')'
              when datatype in ('decimal','numeric') then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+','+convert(varchar(30),isnull(scale,''))+')'
              else LOWER(colname) +' '+datatype
         END) +CHAR(13)   FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name AND isidentity=0 AND colname NOT IN('create_time','update_time','confirm_time','update_user','update_name','confirm_user','confirm_name')
 
  

SELECT @Set=@Set+'Set @'+LOWER(colname)+'=''{'+LOWER(colname)+'}'''+CHAR(13)  FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name  AND isidentity=0 AND colname NOT IN('create_time','update_time','confirm_time','update_user','update_name','confirm_user','confirm_name')


SELECT @Insert=@Insert+','+colname FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name and isidentity=0  AND colname NOT IN('update_time','confirm_time','update_user','update_name','confirm_user','confirm_name')

SELECT @Values=@Values+CASE WHEN colname IN('create_time') THEN ',getdate()' else ',@'+LOWER(colname) END   FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name  AND isidentity=0  AND colname NOT IN('update_time','confirm_time','update_user','update_name','confirm_user','confirm_name')


SET @Rst=
'
/*
{
"状态":0,
"名称":"新增-",
"作者":"",
"创建时间":"'+CONVERT(NVARCHAR(10),GETDATE(),121)+'",
"功能":"新增-",
"log": [
	{"date":"" , "dev" :"" , "content" : ""}
]
}
*/


'
SET @Rst=@Rst+@Declare+CHAR(13)+@Set+CHAR(13)+'INSERT INTO '+QUOTENAME(@table_name)+'('+STUFF(@Insert,1,1,'')+') VALUES('+STUFF(@Values,1,1,'')+')'
 
select @Rst as cmd  

 
 